/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.FinderException;
import javax.ejb.NoSuchEntityException;
import javax.ejb.RemoveException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import transfer.Address;

/**
 *
 * @author Sergey, Oleh
 */
public class AddressBean implements EntityBean {

    private EntityContext entityContext;
    private DataSource dataSource;
    private Address address = new Address();

    public Address getAddress() {
        return address;
    }

    public void setAddress(Address address) {
        this.address = address;
    }

    public void setEntityContext(EntityContext entityContext) throws EJBException {
        try {
            this.entityContext = entityContext;
            Context context = new InitialContext();
            dataSource = (DataSource) context.lookup("jdbc/ossystem");
        } catch (NamingException ex) {
            throw new EJBException("Does not find jdbc/ossystem");
        }
    }

    public void unsetEntityContext() {
        entityContext = null;
    }

    public void ejbRemove() throws RemoveException, EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "DELETE FROM Address WHERE id_address = ?");
            statement.setInt(1, address.getIdAddress().intValue());
            if (statement.executeUpdate() < 1) {
                throw new RemoveException("Deleting error. Can not remove address");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbActivate() {
    }

    public void ejbPassivate() {
    }

    public void ejbLoad() throws EJBException {
        Integer primaryKey = (Integer) entityContext.getPrimaryKey();
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT country, area, city, street, building, flat, longitude, latitude "
                    + "FROM Address WHERE id_address = ?");
            statement.setInt(1, primaryKey.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new NoSuchEntityException("Address with id = " + primaryKey + " not exists");
            }
            address.setIdAddress(primaryKey);
            address.setCountry(resultSet.getString(1));
            address.setArea(resultSet.getString(2));
            address.setCity(resultSet.getString(3));
            address.setStreet(resultSet.getString(4));
            address.setBuilding(resultSet.getString(5));
            address.setFlat(resultSet.getString(6));
            address.setLongitude(new Double(resultSet.getDouble(7)));
            address.setLatitude(new Double(resultSet.getDouble(8)));
        } catch (SQLException ex) {
            throw new EJBException("Error in SELECT");
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbStore() throws EJBException {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "UPDATE Address SET country = ?, area = ?, city = ?, street = ?, building = ?, "
                    + "flat = ?, longitude = ?, latitude = ? "
                    + "WHERE id_address = ?");
            statement.setString(1, address.getCountry());
            statement.setString(2, address.getArea());
            statement.setString(3, address.getCity());
            statement.setString(4, address.getStreet());
            statement.setString(5, address.getBuilding());
            statement.setString(6, address.getFlat());
            statement.setDouble(7, address.getLongitude().doubleValue());
            statement.setDouble(8, address.getLatitude().doubleValue());
            statement.setInt(9, ((Integer) entityContext.getPrimaryKey()).intValue());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection, statement);
        }
    }

    public Collection ejbFindByCoordinates(Double longitude, Double latitude) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        List list = new ArrayList();
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_address "
                    + "FROM Address WHERE longitude = ? AND latitude = ?");
            statement.setDouble(1, longitude.doubleValue());
            statement.setDouble(2, latitude.doubleValue());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(new Integer(resultSet.getInt(1)));
            }
            if (list.isEmpty()) {
                throw new FinderException("Addresses with this primaryKey is not exists");
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public java.lang.Integer ejbFindByPrimaryKey(Integer idAddress) throws FinderException {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer id = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(
                    "SELECT id_address "
                    + "FROM Address WHERE id_address = ?");
            statement.setInt(1, idAddress.intValue());
            ResultSet resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new FinderException("Address with this primaryKey is not exists");
            } else {
                id = new Integer(resultSet.getInt(1));
            }
            return id;
        } catch (SQLException e) {
            e.printStackTrace();
            return id;
        } finally {
            closeConnection(connection, statement);
        }
    }

    public void ejbPostCreate(Address address) {
    }

    public java.lang.Integer ejbCreate(Address address)
            throws CreateException {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer idAddress = null;
        try {
            connection = dataSource.getConnection();
            Statement stat = connection.createStatement();
            ResultSet resultSet = stat.executeQuery("select seq.nextval from dual");

            if (resultSet.next()) {
                idAddress = new Integer(resultSet.getInt(1));
            }
            stat.close();
            address.setIdAddress(idAddress);
            this.address = address;
            statement = connection.prepareStatement("INSERT INTO Address"
                    + "(id_address,country,area,city,street,building,flat,longitude,latitude) VALUES(?, ?, ?, ?,?,?,?,?,?)");
            statement.setInt(1, idAddress.intValue());
            statement.setString(2, address.getCountry());
            statement.setString(3, address.getArea());
            statement.setString(4, address.getCity());
            statement.setString(5, address.getStreet());
            statement.setString(6, address.getBuilding());
            statement.setString(7, address.getFlat());
            statement.setDouble(8, address.getLongitude().doubleValue());
            statement.setDouble(9, address.getLatitude().doubleValue());
            if (statement.executeUpdate() != 1) {
                throw new CreateException("Can not create Address");
            }
            return idAddress;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeConnection(connection, statement);
        }
    }

    private void closeConnection(Connection connection, Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void ejbHomeRemove(Address address) {
        this.address = address;
        try {
            ejbRemove();
        } catch (RemoveException ex) {
            ex.printStackTrace();
        } catch (EJBException ex) {
            ex.printStackTrace();
        }
    }
}
